﻿Public Class Devolucion
    Dim conexion_bd As String

    Enum grabado
        ok
        errores
    End Enum

    Enum estado
        insertar
        modificar
    End Enum

    Enum termino
        aprobado
        rechazado
    End Enum

    Dim accion As estado = estado.modificar
    Dim grabar As grabado = grabado.errores

    Private Sub btn_buscarcli_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscarcli.Click
        grilla.Rows.Clear()
        If Me.txt_dni.Text = "" Or (Not (IsNumeric(Me.txt_dni.Text))) Then
            MessageBox.Show("Número de cliente incorrecto o incompleto", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Me.buscar_clientes()
        End If
    End Sub

    Private Sub buscar_clientes()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select apellido+', '+nombre as nombres, id_cliente from Cliente where num_doc= '" & Me.txt_dni.Text & "' "
        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())

        If IsNumeric(Me.txt_dni.Text) = False Then
            MessageBox.Show("Número de documento inválido", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.txt_dni.Focus()
        Else
            If tabla.Rows.Count > 0 Then 'me fijo si se recuperó algo
                Me.txt_cliente.Text = tabla.Rows(0)("nombres")
                Me.txt_idcliente.Text = tabla.Rows(0)("id_cliente")
                Me.buscarPrestamos()
            Else
                MessageBox.Show("El Cliente no existe", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Me.txt_dni.Focus()
            End If
        End If
        conexion.Close()
    End Sub

    Private Sub buscarPrestamos()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "SELECT DetallePrestamo.IdPrestamo as CodPrestamo, DetallePrestamo.ISBN as ISBN, DetallePrestamo.Estado as Estado, Libro.titulo as Titulo, Prestamo.FechaInicio as FechaInicio, Prestamo.FechaFin as FechaFin FROM Libro INNER JOIN DetallePrestamo ON Libro.ISBN = DetallePrestamo.ISBN INNER JOIN Prestamo ON DetallePrestamo.IdPrestamo = Prestamo.IdPrestamo INNER JOIN Cliente ON Prestamo.IdCliente = Cliente.id_cliente WHERE(Cliente.id_cliente = " & Me.txt_idcliente.Text & ") AND (DetallePrestamo.Estado = 'Abierto')"

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())

        If tabla.Rows.Count > 0 Then

            For i As Integer = 0 To tabla.Rows.Count - 1 Step +1
                grilla.Rows.Add(tabla.Rows(i)("CodPrestamo"), tabla.Rows(i)("ISBN"), tabla.Rows(i)("Titulo"), tabla.Rows(i)("Estado"), Date.Parse(tabla.Rows(i)("FechaInicio")).Day & "-" & Date.Parse(tabla.Rows(i)("FechaInicio")).Month & "-" & Date.Parse(tabla.Rows(i)("FechaInicio")).Year, Date.Parse(tabla.Rows(i)("FechaFin")).Day & "-" & Date.Parse(tabla.Rows(i)("FechaFin")).Month & "-" & Date.Parse(tabla.Rows(i)("FechaFin")).Year)
            Next
        Else
            MessageBox.Show("Actualmente no existen préstamos abiertos para el cliente elegido.", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub Devolucion_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.conexion_bd = "Provider=SQLOLEDB;Data Source=GTºN1\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=C:\DB\LIBRERIA_MAGOO.MDF"

        Me.habilitar(False)
        Me.btn_nuevo.Focus()
    End Sub

    Private Sub habilitar(ByVal x As Boolean)
        Me.txt_cliente.Enabled = x
        Me.txt_idcliente.Enabled = x
        Me.txt_dni.Enabled = x
        Me.btn_nuevo.Enabled = Not x
        Me.btn_buscarcli.Enabled = x
        Me.btn_devolucion.Enabled = x
        Me.grilla.Enabled = x

        Me.txt_cliente.Text = ""
        Me.txt_dni.Text = ""
        Me.txt_idcliente.Text = ""

        Me.grilla.Rows.Clear()
    End Sub

    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nuevo.Click
        Me.habilitar(True)
        Me.txt_dni.Focus()
        Me.accion = estado.insertar
    End Sub

    Private Sub btn_devolucion_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_devolucion.Click
        If (IsNothing(grilla.CurrentRow) = False) Then
            If (MessageBox.Show("¿Desea registrar la devolución del libro con ISBN : " & grilla.CurrentRow.Cells(1).Value & "?", "Pregunta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes) Then
                cambiarEstadoPrestamo()
                registrarDevolucion()
                grilla.Rows.Remove(grilla.CurrentRow)
                MessageBox.Show("La devolución se registró con éxito", "Éxito", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Else
            MessageBox.Show("Debe seleccionar un préstamo de la grilla para registrar una devolución.", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub cambiarEstadoPrestamo()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "update DetallePrestamo set Estado='Cerrado' where IdPrestamo = " & grilla.CurrentRow.Cells(0).Value & " and ISBN = " & grilla.CurrentRow.Cells(1).Value

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        command.ExecuteNonQuery()

        conexion.Close()

    End Sub

    Private Sub registrarDevolucion()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "insert into Devoluciones (IdCliente, ISBN, FechaDevolucion, IdPrestamo) values (" & txt_idcliente.Text & ", " & grilla.CurrentRow.Cells(1).Value & ", '" & Date.Now.Date & "', " & grilla.CurrentRow.Cells(0).Value & ")"
        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        command.ExecuteNonQuery()

        conexion.Close()
    End Sub

    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        If MessageBox.Show("Cerrar esta ventana?", "Importante", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) Then
            Me.Close()
        End If
    End Sub
End Class